package net.chery.update.tool.qa;

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import org.apache.commons.lang3.StringUtils;

import java.io.FileWriter;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author gai
 * 满意度调研系统
 */
public class QATool {
    public static void main(String[] args) throws Exception {
        List<UserRecordInfo> list = readFile("D:/xny.xlsx");
        try (FileWriter fileWriter = new FileWriter("D:/xny.sql")) {
            list.forEach(e -> {
                String del = String.format("delete from user_record_info where vin_no='%s';\r\n", e.getVinNo());
                UserRecordInfoEntity entity = UserRecordInfoEntity.builder()
                        .vinNo(e.getVinNo())
                        .brand(e.getBrand())
                        .brandName(e.getBrand() != null ? UserRecordMap.brandMap.get(e.getBrand()) : null)
                        .carTypeDesc(e.getCarTypeDesc())
                        .notificationNo(e.getNotificationNo())
                        .materialDesc(e.getMaterialDesc())
                        .motor(e.getMotor())
                        .color(e.getColor())
                        .name(e.getName())
                        .phone(e.getPhone())
                        .gender(e.getGender())
                        .genderName(e.getGender() != null ? UserRecordMap.genderMap.get(e.getGender()) : null)
                        .owningCarAge(UserRecordMap.carAgeMap.get(e.getOwningCarAge()))
                        .educationLevel(e.getEducationLevel())
                        .educationLevelName(e.getEducationLevel() != null ? UserRecordMap.educationLevelMap.get(e.getEducationLevel()) : null)
                        .province(e.getProvince())
                        .provinceName(e.getProvinceName())
                        .city(e.getCity())
                        .cityName(e.getCityName())
                        .address2(e.getAddress())
                        .credentialNumber(e.getCredentialNumber())
                        .saleServiceBusiness(e.getSaleServiceBusiness())
                        .saleServiceBusinessName(e.getSaleServiceBusiness() != null ? UserRecordMap.saleServiceBusinessMap.get(e.getSaleServiceBusiness()) : null)
                        .recordBusinessErp(e.getRecordBusinessErp())
                        .recordBusiness(e.getRecordBusiness())
                        .productDate(StringUtils.isEmpty(e.getProductDate()) ? null : DateUtil.parse(e.getProductDate()))
                        .billDate(StringUtils.isEmpty(e.getBillDate()) ? null : DateUtil.parse(e.getBillDate()))
                        .buyCarType(e.getBuyCarType())
                        .buyCarTypeName(e.getBuyCarType() != null ? UserRecordMap.buyCarTypeMap.get(e.getBuyCarType()) : null)
                        .isSpecialSale(e.getIsSpecialSale())
                        .isSpecialSaleName(e.getIsSpecialSale() != null ? UserRecordMap.isSpecialSaleMap.get(e.getIsSpecialSale()) : null)
                        .purchasePurpose(e.getPurchasePurpose())
                        .purchasePurposeName(e.getPurchasePurpose() != null ? UserRecordMap.purchasePurposeMap.get(e.getPurchasePurpose()) : null)
                        .vehicleProperty(e.getVehicleProperty())
                        .vehiclePropertyName(e.getVehicleProperty() != null ? UserRecordMap.vehiclePropertyMap.get(e.getVehicleProperty()) : null)
                        .price(e.getPrice())
                        .fileDate(e.getFileDate())
                        .entryMode(e.getEntryMode())
                        .build();
                Field[] fields = UserRecordInfoEntity.class.getDeclaredFields();
                StringBuilder sb = new StringBuilder("insert into user_record_info(");
                StringBuilder values = new StringBuilder(" values(");
                for (Field field : fields) {
                    TableField tableField = field.getAnnotation(TableField.class);
                    sb.append(tableField.value()).append(",");
                    field.setAccessible(true);
                    Object value = null;
                    try {
                        value = field.get(entity);
                    } catch (IllegalAccessException ex) {
                        throw new RuntimeException(ex);
                    }
                    if (value != null) {
                        Class t = field.getType();
                        if (t == String.class) {
                            values.append("'").append(value).append("',");
                        } else if (t == Date.class) {
                            values.append("'").append(DateUtil.format((Date) value, "yyyy-MM-dd HH:mm:ss")).append("',");
                        } else {
                            values.append(value).append(",");
                        }
                    } else {
                        values.append("null,");
                    }
                }
                String sql = String.format("%s) %s);\r\n", sb.substring(0, sb.length() - 1), values.substring(0, values.length() - 1));
                try {
                    fileWriter.write(del);
                    fileWriter.write(sql);
                } catch (IOException ex) {
                    throw new RuntimeException(ex);
                }
            });

        }
    }

    public static List<UserRecordInfo> readFile(String fileName) {
        List<UserRecordInfo> list = new ArrayList<>();
        // 写法1：JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
        // 具体需要返回多少行可以在`PageReadListener`的构造函数设置
        try {

            EasyExcel.read(fileName, UserRecordInfo.class, new PageReadListener<UserRecordInfo>(dataList -> {
                list.addAll(dataList);
            })).sheet().doRead();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}
